Re: [SQL] How match percent sign in SELECT using LIKE? - Mailing list pgsql-sql
From | Herouth Maoz |
---|---|
Subject | Re: [SQL] How match percent sign in SELECT using LIKE? |
Date | |
Msg-id | l03110702b313f52c7d81@[147.233.159.109] Whole thread Raw |
In response to | Re: [SQL] How match percent sign in SELECT using LIKE? (Bruce Momjian <maillist@candle.pha.pa.us>) |
Responses |
Re: [SQL] How match percent sign in SELECT using LIKE?
(Bruce Momjian <maillist@candle.pha.pa.us>)
|
List | pgsql-sql |
At 17:45 +0200 on 15/3/99, Bruce Momjian wrote: > I have overhauled the LIKE code. %% is not a literal %, but is the same > as wildcard %. Literal % is \%. This is not SQL 92 compliant, is it? The standard states that if you don't have an ESCAPE part to the like (i.e. abc LIKE 'bla\%%' ESCAPE '\'), then there is no escape. I think the correct thing to do is to support the ESCAPE clause: - - - begin quotation - - - Format <like predicate> ::= <match value> [ NOT ] LIKE <pattern> [ ESCAPE <escape character> ] <match value> ::= <character value expression> <pattern> ::= <character value expression> <escape character> ::= <character value expression> [snip] a) If an <escape character> is specified, then: i) If the length in characters of E is not equal to 1, then an exception condition is raised: data exception-invalid escape character. ii) If there is not a partitioning of the string P into sub- strings such that each substring has length 1 or 2, no substring of length 1 is the escape character E, and each substring of length 2 is the escape character E followed by either the escape character E, an <underscore> character, or the <percent> character, then an exception condition is raised: data exception-invalid escape sequence. If there is such a partitioning of P, then in that parti- tioning, each substring with length 2 represents a single occurrence of the second character of that substring. Each substring with length 1 that is the <underscore> character represents an arbitrary character specifier. Each substring with length 1 that is the <percent> character represents an arbitrary string specifier. Each substring with length 1 that is neither the <underscore> character nor the <per- cent> character represents the character that it contains. b) If an <escape character> is not specified, then each <under- score> character in P represents an arbitrary character spec- ifier, each <percent> character in P represents an arbitrary string specifier, and each character in P that is neither the <underscore> character nor the <percent> character represents itself. - - - end quotation - - - Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma